Charting with multiple series
Introduction
This example demonstrates how to create the chart shown below that requires multiple series of data and datasets.
The chart displays sales totals for individual employees as a bar chart with each color representing sales for a fiscal year. The chart includes sales from fiscal years 2006 – 2008. This chart does not require input selections from the user.
A series of data will be created for each fiscal year, with a dataset associated with each series.
The chart uses the same database tables and similar DataBlocks to those used in the Summing and Grouping example (found later in this document under Advanced Query Techniques – Example 8) that reports sales totals grouped by employee ID. Please read the Summing and Grouping example before proceeding further with this example.
Create the Chart Object
The Chart Wizard was demonstrated in the Argos Report Writer Guide. It is recommended that you go through that guide fist, if you have not already done so. For this example, we will be using a classic Argos chart. For information on how to create a standard chart, please refer to the Chart Wizard section of the Argos Help.
- Click the chart icon, then click within the Design Area to create the chart object. Expand the object to the desired size.
- Double-click the object to launch the Chart Wizard.
Name the first series
- Click the green plus icon to create a series.
- Change the Series name to fy2006
- Click the hardhat/hammer to create a new dataset.
- Enter ‘fy2006’ as the name of the dataset and click OK.
- Select SQL Statement and click OK to launch the SQL Editor.
- Click the Build Query icon to launch the Build Query dialog box.
Creating the new dataset for the first series
Within the Build Query dialog box, create the query shown in the figure below using the Employees, Orders, Order_Details, and Products tables that were used in the Summing and Grouping example.
The SELECT clause:
The WHERE clause:
Tip: The use of the '#' delimiter before and after the date is the required syntax for MS-Access (which uses the Jet database engine). Other databases may require a different delimiter.
This completes the query for the fy2006 dataset. Later, queries will be developed for fy2007 and fy2008 with the only difference being in the WHERE clause where the corresponding date range will be changed. The queries for years 2007 and 2008 will be pasted from the first query that was developed for fy2006. After pasting, the date range shown above will be modified to reflect the corresponding fiscal year.
Click OK and validate the query and return to the Chart Wizard.
Select the Chart Types, labels, and other options
- On the Data tab of the Chart Wizard, Select 'total_sales' for the Value field.
- Select 'emp_id' for the Label field.
- Under the Type tab, select the Bar Chart radio button and click on the “Sides” Chart Sub Type.
- Under the Labels tab, uncheck the Visible Label box. This removes the labels that are on the bars.
- Click the Next button to view the Chart Theme and Panel selections. Choose whatever theme and palette you desire.
- Click the Next button and enter the titles. This example used “Sales Total” for the vertical axis title and “Employee ID” for the horizontal axis title.
- Click the Finish button to complete the design of the first series. This brings you back to the Argos DataBlock Designer.
- Save and Test if you desire.
This completes the series and dataset for fiscal year 2006. When you are ready to create the chart for the next series, double-click on the chart to launch the Chart Wizard again.
Create the next series for fy2007
Before beginning the design for fy2007 you need to copy the fy2006 dataset.
- Click fy2006 under “Series” and click the hardhat/hammer icon to launch the SQL Editor.
- Click Edit Visual Design to launch the Build Query dialog.
- Click the Copy icon at the top of the dialog box.
- Click Close to return to the SQL Editor. Click Close again to return to the Chart Wizard.
- Click the green plus icon to add a new series.
- Name the new series 'fy2007'.
- Select fy2007 from the list of series at the top of the screen.
- In the Dataset field, select “Create a new dataset…” then click the hardhat/hammer icon .
- Name the new dataset 'fy2007' and click OK.
- Select SQL Statement and click OK to launch the SQL Editor.
- Click the Build Query icon to launch the Build Query editor.
- Click Paste to paste the dataset created for fy2006.
- Within the Build Query dialog box, click the WHERE tab and change the year in each date from 2006 to 2007.
- Click OK to exit the Build Query editor. Click OK again and validate the query to return to the Chart Wizard.
- Select total_sales for the Value and select emp_id for the Label.
- Under the Type tab, select Bar Chart and Sides for the type and sub type, respectively.
- Click the Labels tab and uncheck 'Visible Label'.
- Click Next and adjust the colors and theme as desired. The bars for the second series should be a different color to distinguish them from the first series.
The dataset for fy2007 is now complete. Repeat this process for the third dataset (fy2008).
Summary
This example illustrated how to create several data series to be displayed on one chart and how to associate datasets with each series. Save this as a Dashboard.
Note: Do not mix chart styles among series on the same chart. For instance, if series 1 is a bar chart and series 2 is a line chart, both styles will appear in the plot area, which is not desirable.
Note: Be sure that the correct Series is highlighted when editing otherwise you may be unintentionally changing a Series that you didn’t want to change.